package edu.nf.db.util;

import java.sql.*;

/**
 * SQl执行器，框架的核心入口类
 *
 * @author zhu
 * @date 2024-05-08
 */
public class SqlExecutor {

    private final Connection connection;

    /**
     * 构造方法传入一个Connection对象
     *
     * @param connection 数据库连接对象
     */
    public SqlExecutor(Connection connection) {
        this.connection = connection;
    }

    /**
     * 执行增删改操作
     *
     * @param sql SQL语句
     * @param params SQL语句所需要的参数
     * @return 受影响行数
     * @throws SQLException 运行时产生SQL异常
     */
    public int executeUpdate(String sql, Object... params) throws SQLException {
        if (connection == null){
            throw new RuntimeException("Null connection.");
        }
        if (sql == null || sql.isEmpty()){
            throw new RuntimeException("Null SQL statement.");
        }
        // 声明 PreparedStatement
        PreparedStatement ps = null;
        try {
            // 预编译发送SQL并返回ps对象
            ps = connection.prepareStatement(sql);
            // 设置参数
            setParameters(ps, params);
            // 执行SQL返回受影响的行数
            return ps.executeUpdate();
        } catch (SQLException e){
            // 异常重抛
            throw new RuntimeException("execute fail." + e);
        } finally {
            // 关闭资源
            close(ps);
            close();
        }
    }

    /**
     * 批量操作
     *
     * @param sql SQL语句
     * @param params 批量参数
     * @return 多条受影响行数
     * @throws SQLException 运行时产生的SQL异常
     */
    public int[] executeBatch(String sql, Object[][] params) throws SQLException {
        if (connection == null){
            throw new RuntimeException("Null connection.");
        }
        if (sql == null || sql.isEmpty()){
            throw new RuntimeException("Null SQL statement.");
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            // 批量设置参数
            for (Object[] param : params){
                setParameters(ps, param);
                // 将ps的操作缓存到内存中，最后在批量提交到数据库中
                ps.addBatch();
            }
            // 批量执行
            return ps.executeBatch();
        } catch (SQLException e){
            throw new RuntimeException("execute fail." + e);
        } finally {
            close(ps);
            close();
        }
    }

    /**
     * 查询
     *
     * @param <T> 所需的类型
     * @param sql SQL语句
     * @return 查询结果
     * @throws SQLException 运行时产生的SQL异常
     */
    public <T> T executeQuery(String sql, ResultSetHandler<T> handler,Object... params) throws SQLException {
        if (connection == null){
            throw new RuntimeException("Null connection.");
        }
        if (sql == null || sql.isEmpty()){
            throw new RuntimeException("Null SQL statement.");
        }
        if (handler == null){
            throw new RuntimeException("Null ResultSetHandler.");
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        T result = null;
        try {
            ps = connection.prepareStatement(sql);
            // 设置参数
            setParameters(ps, params);
            // 执行查询并返回结果集
            rs = ps.executeQuery();
            // 将结果集交给结果集处理器去处理并返回处理结果
            result = handler.handle(rs);
            return result;
        } catch (SQLException e){
            throw new RuntimeException("execute fail." + e);
        } finally {
            close(rs);
            close(ps);
            close();
        }
    }

    /**
     * 设置SQL的参数
     *
     * @param ps PreparedStatement 对象
     * @param params 参数集合
     * @throws SQLException SQL异常
     */
    private void setParameters(PreparedStatement ps, Object... params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            ps.setObject(i + 1, params[i]);
        }
    }

    private void close(ResultSet rs){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException("Close resultSet fail." + e);
            }
        }
    }

    private void close(Statement st){
        if (st != null){
            try {
                st.close();
            } catch (SQLException e) {
                throw new RuntimeException("Close statement fail." + e);
            }
        }
    }

    private void close(){
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException("Close connection fail." + e);
            }
        }
    }
}
